<?php
namespace Admins\Model;
use Think\Model;
class ReportformModel extends Model {
	protected $tableName = 'reportform';
	
/*	public function _initialize()
	{
		set_time_limit(900);
		ini_set("memory_limit", "1024M");
		vendor('PHPExcel.PHPExcel','','.php');
	}
*/
	public function onlyCheck($orderNumber)
	{//以订单号，确保数据库没有重复
		$sql = "select id,orderNumber from ".C('DB_PREFIX')."reportform where orderNumber = '".$orderNumber."'";
		$data = $this->query($sql);	
		//$data = $this->execute($sql);	
		if($data)
		{
			return $data[0];
		}
		else
		{
			return false;
		}	
	}
	public function getInsertSql($data)
	{//只返回sql语句,不执行
		
		$sql = $this->fetchSql(true)->add($data);
		return $sql;	
	}
	public function getUpdateSql($data)
	{
		$sql = $this->fetchSql(true)->save($data);
		return $sql;		
	}
	public function moreExecute($arrSql)
	{//数组形式接受sql语句
		$sql = $arrSql[0];
		$data = $this->execute($sql);	
		if($data)
		{
			return true;
		}
		else
		{
			return false;
		}		
			
	}
	public function getLists($qun=0,$startTime=0,$endTime=0)
	{//都必须有值 //都为0 读所有数据
		$where = ' 1 ';
		$orderBy = ' ORDER BY `clickTime` DESC ';
		if($qun<>0)
		{
			$where .= "and  groupName = '".$qun."微信群'";
		}		
		if($startTime<>0 && $endTime<>0)
		{
			$where .= " and clickTime BETWEEN '".$startTime."' AND '".$endTime."' ";
		}
		//import('ORG.Util.Page');// 导入分页类
		
		$sql1 = "SELECT `id` FROM  `think_reportform` WHERE " .$where;
		$count = $this->execute($sql1);

		//$Page       = new \Think\Page($count,10);// 实例化分页类 传入总记录数和每页显示的记录数
		$Page       = new \Common\Util\DiyPage($count,10);
		//分页跳转的时候保证查询条件
		foreach($map as $key=>$val) {
			//$Page->parameter   .=   "$key=".urlencode($val).'&';
		}
		
		$sql = "SELECT `id`,clickTime,goodsTitle,goodsId,shopName,goodsNums,goodsPrice,orderStatus,splitRatio,paymentAmount,settlementAmount,settlementTime,commissionRate,commissionAmount,orderNumber,groupName,orderType,payStatus  FROM  `think_reportform` WHERE ".$where.$orderBy." 
LIMIT ".$Page->firstRow.','.$Page->listRows;
		//echo $sql;
//INNER JOIN 		
		$page = $Page->show();// 分页显示输出
		$list = $this->query($sql);
		$result['list'] = $list ;
		$result['page'] = $page; 
		return $result;		
	
	}
	public function UpdataXls($dir)
	{//处理报表
		$PHPExcel = new \Admins\Model\PHPExcelModel();
		$res = $PHPExcel->getExcelAllData($dir);
		$conf = $res[1];
		//var_dump($conf );
		
		//var_dump($res);
		//获取读取的规则--配置有关系		
		$fields = c("reportform");
		foreach ($fields as $k=>$v)
		{
			$ruleField[$k] = $v;
		}		
		$rowKey = getArrKey($conf,$ruleField);
		//var_dump($rowKey);
		
		unset($res[1]); //删除表头		
		//定义新的录入数据格式
		$data = '';
		foreach($res as $k=>$v)
		{
			$reportData['clickTime'] =  $v[$rowKey['clickTime']];
			$reportData['goodsTitle'] = $v[$rowKey['goodsTitle']];
			$reportData['goodsId'] = $v[$rowKey['goodsId']];
			$reportData['shopName'] = $v[$rowKey['shopName']];
			$reportData['goodsNums'] = $v[$rowKey['goodsNums']];
			$reportData['goodsPrice'] = $v[$rowKey['goodsPrice']];
			$reportData['orderStatus'] = $v[$rowKey['orderStatus']];
			$reportData['splitRatio'] = $v[$rowKey['splitRatio']];			
			$reportData['paymentAmount'] = $v[$rowKey['paymentAmount']];
			$reportData['settlementAmount'] = $v[$rowKey['settlementAmount']];
			$reportData['settlementTime'] = $v[$rowKey['settlementTime']];
			$reportData['commissionRate'] = ($v[$rowKey['commissionRate']]/2)."%";
			$reportData['commissionAmount'] = ($v[$rowKey['commissionAmount']]/2);
			$reportData['orderNumber'] = $v[$rowKey['orderNumber']];
			$reportData['groupName'] = $v[$rowKey['groupName']];
			$reportData['orderType'] = $v[$rowKey['orderType']];

			
			$data[$k] = $reportData;
		}
		//var_dump($data);		
		$sql = '';	
		if($data == "") return false;
		foreach($data as $v)
		{

			$isNewdata = $this->onlyCheck($v['orderNumber']);
			
			if($isNewdata)
			{//更新操作语句
				$v['id'] = $isNewdata['id'];
				$sql .= $this->getUpdateSql($v).';';
			}
			else
			{//插入操作语句
				
				$sql .= $this->getInsertSql($v).';';
				
				
			}		
		}
		//echo $sql;
		//定义一个数组，把这些sql语句以一个引用（指针）传过去，不然直接把字符串当参数太大，太消耗内存了。
		$arraySql[] = $sql;
		//var_dump($arraySql);
		$result = $this->moreExecute($arraySql);	
		//var_dump($result );
		if($result)
		{
			return true;
		}
		else
		{
			return false;
		}	
	
	}
	
	
	
	public function SetUpddateTime($time)
	{//前段浏览是报表的实时更新配合软件
		$sql = "SELECT `id` FROM  ".C('DB_PREFIX')."reportform_uptime WHERE 1 ORDER BY id ASC LIMIT 0 , 1";
		$count = $this->query($sql);		
		//var_dump($count);
		if(empty($count))
		{//做插入操作
			$sql = "insert into ".C('DB_PREFIX')."reportform_uptime (`updatetime`) values ('".$time."')";
		}
		else
		{//做更新操作
			$id = $count[0];
			$id = $id['id'];
			if($id=='') {$id = 1;}
			$sql = "UPDATE ".C('DB_PREFIX')."reportform_uptime SET `updatetime`= '".$time."' where id = ".$id;
		}
		
		$data = $this->execute($sql);	
		if($data)
		{
			return true;
		}
		else
		{
			return false;
		}	
	}
	public function getUpddateTime()
	{
		$sql = "SELECT `updatetime` FROM  ".C('DB_PREFIX')."reportform_uptime WHERE 1 ORDER BY id ASC LIMIT 0 , 1";
		$data = $this->query($sql);	
		//$data = $this->execute($sql);	
		if($data)
		{
			return $data[0];
		}
		else
		{
			return false;
		}	
	}
	public function qunPayCount($qun=0,$flag=1)
	{//兼职报表统计前台看
		$where = " 1 "; 
		if($qun<>0)
		{
			$where = $where." and groupName = '".$qun."' ";
		}
		if($flag == 1)
		{
			$sql = "select count(id) as id,SUM(commissionAmount) as commissionAmount  from ".C('DB_PREFIX')."reportform where ".$where." and orderstatus = '订单付款' and clickTime > '".date("Y-m-d H:i:s",strtotime("-600 day"))."'";
		}
		if($flag == 2)
		{
			$sql = "select count(id) as id,SUM(commissionAmount) as commissionAmount from ".C('DB_PREFIX')."reportform where ".$where." and orderstatus = '订单结算' and payStatus='0' and clickTime > '".date("Y-m-d H:i:s",strtotime("-600 day"))."'";
		}
		if($flag == 3)
		{
			$sql = "select count(id) as id,SUM(commissionAmount) as commissionAmount from ".C('DB_PREFIX')."reportform where ".$where." and orderstatus = '订单结算' and payStatus='1' and clickTime > '".date("Y-m-d H:i:s",strtotime("-600 day"))."'";
		}
		if($flag == 4)
		{
			$sql = "select count(id) as id,SUM(commissionAmount) as commissionAmount from ".C('DB_PREFIX')."reportform where ".$where." and orderstatus = '订单结算' and payStatus='2' and clickTime > '".date("Y-m-d H:i:s",strtotime("-600 day"))."'";
		}
		
		
		$data = $this->query($sql);	
		if($data)
		{
			return $data[0];
		}
		else
		{
			return false;
		}		
	}
	public function getListsInfo($qun=0,$payStatus='')
	{

		$where = ' 1 ';
		$orderBy = ' ORDER BY `clickTime` DESC ';
		if($qun<>0 && $qun<>'')
		{
			$where .= " and  groupName = '".$qun."'";
		}
		if($payStatus<>'')
		{
			$where .= " and  payStatus = '".$payStatus."'";
		}

		import('ORG.Util.Page');// 导入分页类
		
		$sql1 = "SELECT `id` FROM  `think_reportform` WHERE " .$where;
		$count = $this->execute($sql1);

		$Page       = new \Think\Page($count,10);// 实例化分页类 传入总记录数和每页显示的记录数
		//分页跳转的时候保证查询条件
		//foreach($map as $key=>$val) {
			//$Page->parameter   .=   "$key=".urlencode($val).'&';
		//}
		
		$sql = "SELECT `id`,clickTime,goodsTitle,goodsId,shopName,goodsNums,goodsPrice,orderStatus,splitRatio,paymentAmount,settlementAmount,settlementTime,commissionRate,commissionAmount,orderNumber,groupName,orderType,payStatus  FROM  `think_reportform` WHERE ".$where.$orderBy." 
LIMIT ".$Page->firstRow.','.$Page->listRows;
		//echo $sql;
//INNER JOIN 		
		$page       = $Page->show();// 分页显示输出
		$list = $this->query($sql);
		$result['list'] = $list ;
		$result['page'] = $page; 
		return $result;		
	
	
	}
	public function DelReportform($datepicker)
	{
		$sql =  "delete FROM ".C('DB_PREFIX')."reportform where `clickTime` < '".$datepicker."'";
		//echo $sql;
		$data = $this->execute($sql);	
		if($data)
		{
			return $data;
		}
		else
		{
			return false;
		}	
	
	
	}
	
	
	
	
}